﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.OleDb;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;

namespace 图书管理系统
{
    public partial class 管理员界面 : Form
    {

        User user = 用户登录.user;
        public 管理员界面()
        {
            InitializeComponent();
            dataGridView1.AutoGenerateColumns = false;
            dataGridView1.AllowUserToAddRows = false;
            dataGridView2.AutoGenerateColumns = false;
            dataGridView2.AllowUserToAddRows = false;
            dataGridView3.AutoGenerateColumns = false;
            dataGridView3.AllowUserToAddRows = false;
            dataGridView4.AutoGenerateColumns = false;
            dataGridView4.AllowUserToAddRows = false;
            dataGridView5.AutoGenerateColumns = false;
            dataGridView5.AllowUserToAddRows = false;
            dataGridView6.AutoGenerateColumns = false;
            dataGridView6.AllowUserToAddRows = false;
        }

        private void tabPage1_Click(object sender, EventArgs e)
        {

        }

        private void tabPage2_Click(object sender, EventArgs e)
        {

        }

        //导入文件
        SqlConnection conn = new SqlConnection();
        //DataSet ds = new DataSet();
        DataTable dt;
        private void button1_Click(object sender, EventArgs e)
        {
            System.Windows.Forms.OpenFileDialog fd = new OpenFileDialog();
            if (fd.ShowDialog() == DialogResult.OK)
            {
                string fileName = fd.FileName;
                bind(fileName);
            }
        }
        //将Excel的Sheet3$子表显示出来
        private void bind(string fileName)
        {
            string strConn = "Provider=Microsoft.ACE.OLEDB.12.0;" + "Data Source=" + fileName + ";" + "Extended Properties='Excel 8.0; HDR=Yes; IMEX=1'";
            OleDbDataAdapter da = new OleDbDataAdapter("SELECT *  FROM [Sheet1$]", strConn);
            //"Provider=Microsoft.ACE.OLEDB.12.0" +"Data Source=" + fileName + ";" +"Extended Properties=Excel 8.0";
            // "Provider=Microsoft.Jet.OLEDB.4.0;" +"Data Source=" + fileName + ";" +"Extended Properties='Excel 8.0; HDR=Yes; IMEX=1'";
            DataSet ds = new DataSet();
            try
            {
                da.Fill(ds);
                dt = ds.Tables[0];
                this.dataGridView1.DataSource = dt;
            }
            catch (Exception err)
            {
                MessageBox.Show("操作失败！" + err.ToString());
            }
        }
        //上传到新书记录
        private void button2_Click(object sender, EventArgs e)
        {
            if (dataGridView1.Rows.Count > 0)
            {
                DataRow dr ;
                int b = 0;
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    dr = dt.Rows[i];
                    String ISBN = dr["ISBN"].ToString().Trim();
                    string BookName = dr["BookName"].ToString();
                   // MessageBox.Show(dr["state"].ToString());
                    //上传第i条记录
                    if (dr["state"].ToString() == "0")
                    {
                        b += insertToSql(dr);
                    }
                    else
                    {
                        MessageBox.Show("该书籍已入库");
                    }
                    }
                if (b > 0)
                {
                    MessageBox.Show(b + "条入库成功");
                }
            }
            else
            {
                MessageBox.Show("没有数据！");
            }
        }
        //显示新书信息
        private void button5_Click(object sender, EventArgs e)
        {

            String sql = "select * from NewBookRecord where state=0 ";
            SqlCommand cmd = Dao.SqlConnect(sql, conn);
            conn.Open();
            DataSet ds = new DataSet();
            SqlDataAdapter da = new SqlDataAdapter(cmd);
            da.Fill(ds);
            dt = ds.Tables[0];
           // dataGridView1.AutoGenerateColumns = false;
            dataGridView1.DataSource = ds.Tables[0];
            

            conn.Close();
        }
        //上传到图书信息
        private void button4_Click(object sender, EventArgs e)
        {

            int b = 0;
            if (dataGridView1.Rows.Count > 0)
            {
                DataRow dr;
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    dr = dt.Rows[i];
                    String ISBN = dr["ISBN"].ToString().Trim();
                    //上传第i条记录
                    if (dr["state"].ToString()=="0")
                    {
                        b += insertToBInfo(dr);
                    }
                    else
                    {
                        MessageBox.Show("已入库");
                    }
                }
                if (b > 0)
                {
                    MessageBox.Show(b + "条成功");
                }

               // conn.Close(); 
            }
            else
            {
                MessageBox.Show("没有数据！");
            }
        }
        //Insert插入到表格的具体方法
        //插入到新书记录——状态默认0（未入库）
        private int insertToSql(DataRow dr)
        {
            int i = 0;
            //excel表中的列名和数据库中的列名一定要对应  
          
            string sql = "insert into NewBookRecord(ISBN,BookName,Author,Press,Publication_time,BookIntro,BookPrice) values(@ISBN,@BookName,@Author,@Press,@Publication_time,@BookIntro,@BookPrice)"; 
            SqlCommand cmd = Dao.SqlConnect(sql, conn);
            conn.Open();
            string @ISBN = dr["ISBN"].ToString().Trim();
            string @BookName = dr["BookName"].ToString();
            string @Author = dr["Author"].ToString();
            string @Press = dr["Press"].ToString();
            DateTime @Publication_time = Convert.ToDateTime(dr["Publication_time"]);
            float @BookPrice = Convert.ToSingle(dr["BookPrice"].ToString().Trim());
            string @BookIntro = dr["BookIntro"].ToString();
            cmd.Parameters.Add("@ISBN", SqlDbType.VarChar, 30).Value = @ISBN;
            cmd.Parameters.Add("@BookName", SqlDbType.VarChar, 100).Value = @BookName;
            cmd.Parameters.Add("@Author", SqlDbType.VarChar, 100).Value = @Author;
            cmd.Parameters.Add("@Press", SqlDbType.VarChar, 100).Value = @Press;
            cmd.Parameters.Add("@Publication_time", SqlDbType.DateTime).Value = @Publication_time;
            cmd.Parameters.Add("@BookIntro", SqlDbType.VarChar, 200).Value = @BookIntro;
            cmd.Parameters.Add("@BookPrice", SqlDbType.Float).Value = @BookPrice;
            i = cmd.ExecuteNonQuery();
            if (i > 0)
            {
                dr["state"] = 1;
                dr.Delete();
            }
            
            conn.Close();
            return i;
        }
        //插入到图书信息——状态改为1（已入库），图书信息插入时默认下架1,不可借
        private int insertToBInfo(DataRow dr)
        {
            try
            {
            string sql = "insert into Binfo_Table(ISBN,BookName,Author,Press,Publication_time,BookIntro,BookPrice) values(@ISBN,@BookName,@Author,@Press,@Publication_time,@BookIntro,@BookPrice)";
            //string sql = "insert into NewBookRecord values('" + ISBN + "','" + BookName + "','" + Author + "','" + Press + "','" + Publication_time + "','" + BookIntro + "')";
            SqlCommand cmd = Dao.SqlConnect(sql, conn);
                conn.Open();
            //excel表中的列名和数据库中的列名一定要对应
            int id = Convert.ToInt32(dr["id"].ToString().Trim());
            string @ISBN = dr["ISBN"].ToString().Trim();
            string @BookName = dr["BookName"].ToString();
            string @Author = dr["Author"].ToString();
            string @Press = dr["Press"].ToString();
            DateTime @Publication_time = Convert.ToDateTime(dr["Publication_time"]);
            float @BookPrice = Convert.ToSingle(dr["BookPrice"].ToString().Trim());
            string @BookIntro = dr["BookIntro"].ToString();
            cmd.Parameters.Add("@ISBN", SqlDbType.VarChar, 30).Value = @ISBN;
            cmd.Parameters.Add("@BookName", SqlDbType.VarChar, 100).Value = @BookName;
            cmd.Parameters.Add("@Author", SqlDbType.VarChar, 100).Value = @Author;
            cmd.Parameters.Add("@Press", SqlDbType.VarChar, 100).Value = @Press;
            cmd.Parameters.Add("@Publication_time", SqlDbType.DateTime).Value = @Publication_time;
            cmd.Parameters.Add("@BookIntro", SqlDbType.VarChar, 200).Value = @BookIntro;
            cmd.Parameters.Add("@BookPrice", SqlDbType.Float).Value = @BookPrice;

            int i = cmd.ExecuteNonQuery();
            conn.Close();
            if (i > 0)
            {
                SqlConnection conn1 = new SqlConnection();
                String sql1 = "update NewBookRecord set state = 1  where id=" + id ;  
                SqlCommand cmd1 = Dao.SqlConnect(sql1, conn1);
                conn1.Open();
                int flag = cmd1.ExecuteNonQuery();
                if (flag > 0)
                {
                        dr["state"] = 1;
                        dr.Delete();
                    }
                    conn1.Close();
                }
                return i;
            }
            catch
            {
                return 0;
            }
            finally
            {
                conn.Close();
            }
           
        }

        private void tabPage5_Click(object sender, EventArgs e)
        {
            
        }

        //图书上下架
        private void UpDownBook()
        {
            dataGridView2.Visible = true;
            SqlConnection conn = new SqlConnection();
            string sql = "select * from Binfo_Table order by Shelf desc ";
            dataGridView2.Rows.Clear();
            SqlCommand cmd = Dao.SqlConnect(sql, conn);
            try
            {
                conn.Open();
                DataTable ds = new DataTable();
                SqlDataAdapter da = new SqlDataAdapter(cmd);
                da.Fill(ds);

                foreach (DataRow dataRow in ds.Rows)
                {
                    int index = dataGridView2.Rows.Add();
                    dataGridView2.Rows[index].Cells[0].Value = dataRow["ISBN"].ToString();
                    dataGridView2.Rows[index].Cells[1].Value = dataRow["BookName"].ToString();
                    dataGridView2.Rows[index].Cells[2].Value = dataRow["Author"].ToString();
                    dataGridView2.Rows[index].Cells[3].Value = dataRow["Press"].ToString();
                    dataGridView2.Rows[index].Cells[4].Value = dataRow["Publication_time"].ToString();

                    //DataGridViewButtonColumn shelf = new DataGridViewButtonColumn();
                    //shelf.HeaderText = "下载";
                    // shelf.Name = "downLoadColumn";
                    //  shelf.Text = "下载";
                    // shelf.Width = 80;
                    //  shelf.UseColumnTextForButtonValue = true;
                    //  shelf.DataPropertyName = "_id";
                    //shelf.Tag = shelf.DataPropertyName;
                    //shelf.DisplayIndex = 3;
                    //shelf.Frozen = true;
                    // dataGridView2.Columns.Insert(2,shelf);
                    //dataGridView2.Rows[index].Cells[5].;

                    if (dataRow["Shelf"].ToString() == "0")
                    {
                        dataGridView2.Rows[index].Cells[8].Value = "下架";
                        dataGridView2.Rows[index].Cells[5].Value = "是";
                    }
                    else
                    {
                        dataGridView2.Rows[index].Cells[8].Value = "上架";
                        dataGridView2.Rows[index].Cells[5].Value = "否";
                    }



                    //dataGridView2.Rows[index].Cells[9].Add(new Label);
                    dataGridView2.Rows[index].Cells[6].Value = dataRow["BookLocation"].ToString();
                    dataGridView2.Rows[index].Cells[7].Value = dataRow["BookNum"].ToString();
                    dataGridView2.Rows[index].Cells[9].Value = dataRow["Bid"].ToString();
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
            finally
            {
                conn.Close();
            }
        }
        private void button3_Click(object sender, EventArgs e)
        {
            UpDownBook();
        }

        private void dataGridView2_CellContentClick(object sender, DataGridViewCellEventArgs e)
        {
          
                 if (e.RowIndex >= 0)
            {
                DataGridViewColumn column = dataGridView2.Columns[e.ColumnIndex];  //总数
                int index = dataGridView2.CurrentCell.RowIndex;
                if (column is DataGridViewButtonColumn)
                {
                   
                    //这里可以编写你需要的任意关于按钮事件的操作~
                    int Bid = Convert.ToInt32(dataGridView2.Rows[index].Cells[9].Value.ToString().Trim());
                    String state = dataGridView2.Rows[index].Cells[5].Value.ToString().Trim();

                    //图书上架
                    if (state == "否")
                    {
                        //模拟找到闲置的书架编号
                        SqlConnection conn0 = new SqlConnection();
                        String sql = "select * from shelf where state=0";
                        try
                        {
                            SqlCommand cmd0 = Dao.SqlConnect(sql, conn0);
                            conn0.Open();
                             DataSet dt = new DataSet();
                                SqlDataAdapter sqlDataAdapter = new SqlDataAdapter(cmd0);
                                sqlDataAdapter.Fill(dt, "shelf");
                                String BookNumber = dt.Tables["shelf"].Rows[0]["number"].ToString().Trim();
                                String location = dt.Tables["shelf"].Rows[0]["location"].ToString().Trim();
                            if (BookNumber!=null)
                            {
                                //修改成上架书籍
                                conn0.Close();
                                SqlConnection conn1 = new SqlConnection();
                                String sql1 = "update Binfo_Table set BookNum='"+BookNumber + "',Shelf = 0,BookStatus = '可借'," + "BookLocation='" + location + "' where Bid=" + Bid ;  //需改成按Bid查找
                                SqlCommand cmd1 = Dao.SqlConnect(sql1, conn1);
                                    conn1.Open();
                                    int flag = cmd1.ExecuteNonQuery();
                                    if (flag > 0)
                                    {
                                        //实时更改界面
                                        dataGridView2.Rows[index].Cells[5].Value = "是";
                                        dataGridView2.Rows[index].Cells[6].Value = location;
                                        dataGridView2.Rows[index].Cells[7].Value = BookNumber;
                                    dataGridView2.Rows[index].Cells[8].Value = "下架";
                                    //书架跟新

                                    sql = "update shelf set state=1 where number='"+ BookNumber+"'";
                                     cmd0 = Dao.SqlConnect(sql, conn0);
                                    conn0.Open();
                                    int i = cmd0.ExecuteNonQuery();
                                    if ( i> 0)
                                    {
                                        MessageBox.Show("上架成功！");
                                    }
                                    conn0.Close();
                                }
                                conn1.Close();
                            }
                            else
                            {
                                MessageBox.Show("not Shelf");
                            }
                         }
                        catch (Exception ex)
                        {
                            MessageBox.Show(ex.Message);
                        }
                        finally
                        {
                            conn0.Close();
                        }
                       
                    }
                    //图书下架
                    else
                    {
                       
                        try
                        {
                                //修改成上架书籍
                                SqlConnection conn = new SqlConnection();
                                String sql = "update Binfo_Table set BookNum='" + null + "',Shelf = 1,BookStatus = '不可借'," + "BookLocation='" + null + "' where Bid='" + Bid + "'";  //需改成按Bid查找
                                SqlCommand cmd = Dao.SqlConnect(sql, conn);
                                conn.Open();
                                int flag = cmd.ExecuteNonQuery();
                                if (flag > 0)
                                {  
                                //书架跟新  
                                conn.Close();
                                    sql = "update shelf set state=0 where number='" + dataGridView2.Rows[index].Cells[7].Value.ToString().Trim() + "'";
                                    cmd = Dao.SqlConnect(sql, conn);
                                    conn.Open();
                                    int i = cmd.ExecuteNonQuery();
                                    if (i > 0)
                                    {
                                    //实时更改界面
                                    dataGridView2.Rows[index].Cells[5].Value = "否";
                                    dataGridView2.Rows[index].Cells[6].Value = null;
                                    dataGridView2.Rows[index].Cells[7].Value = null;
                                    dataGridView2.Rows[index].Cells[8].Value = "上架";
                                    MessageBox.Show("下架成功！");
                                }
                                    conn.Close();
                                }
                        }
                        catch (Exception ex)
                        {
                            MessageBox.Show(ex.Message);
                        }
                        finally
                        {
                            conn.Close();
                        }
                    }

                }
            }
        }

        private void 管理员界面_Load(object sender, EventArgs e)
        {

        }
        //图书信息维护
        private void ManageBook()
        {

            SqlConnection conn = new SqlConnection();
            string sql = "select * from Binfo_Table order by Shelf desc ";
            dataGridView4.Rows.Clear();
            SqlCommand cmd = Dao.SqlConnect(sql, conn);
            try
            {
                conn.Open();
                DataTable ds = new DataTable();
                SqlDataAdapter da = new SqlDataAdapter(cmd);
                da.Fill(ds);

                foreach (DataRow dataRow in ds.Rows)
                {
                    int index = dataGridView4.Rows.Add();
                    dataGridView4.Rows[index].Cells[0].Value = dataRow["ISBN"].ToString();
                    dataGridView4.Rows[index].Cells[1].Value = dataRow["BookName"].ToString();
                    dataGridView4.Rows[index].Cells[2].Value = dataRow["Author"].ToString();
                    dataGridView4.Rows[index].Cells[3].Value = dataRow["Press"].ToString();
                    dataGridView4.Rows[index].Cells[4].Value = dataRow["Publication_time"].ToString();

                    if (dataRow["Shelf"].ToString() == "0")
                    {
                        dataGridView4.Rows[index].Cells[5].Value = "是";
                    }
                    else
                    {
                        dataGridView4.Rows[index].Cells[5].Value = "否";
                    }



                    //dataGridView4.Rows[index].Cells[9].Add(new Label);
                    dataGridView4.Rows[index].Cells[6].Value = dataRow["BookLocation"].ToString();
                    dataGridView4.Rows[index].Cells[7].Value = dataRow["BookNum"].ToString();
                    dataGridView4.Rows[index].Cells[10].Value = dataRow["Bid"].ToString();
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
            finally
            {
                conn.Close();
            }
        }
        //管理员显示荐购审核
        private void VerifyRecommendation()
        {
            SqlConnection conn = new SqlConnection();
            string sql = "select * from Purchaserecord_Table order by Recmdat_time desc ";
            //dataGridView5.Rows.Clear();
            SqlCommand cmd = Dao.SqlConnect(sql, conn);
            conn.Open();
            DataSet ds = new DataSet();
            SqlDataAdapter da = new SqlDataAdapter(cmd);
            da.Fill(ds);
            dt = ds.Tables[0];
            // dataGridView5.AutoGenerateColumns = false;
            dataGridView5.DataSource = ds.Tables[0];
            conn.Close();
        }
        private void VerifyBook()
        {
            conn = new SqlConnection();
            string sql = "select * from Claimrecord_Table order by Reply_time desc ";
            //dataGridView6.Rows.Clear();
            SqlCommand cmd = Dao.SqlConnect(sql, conn);
            conn.Open();
            DataSet ds = new DataSet();
            SqlDataAdapter da = new SqlDataAdapter(cmd);
            da.Fill(ds);
            dt = ds.Tables[0];
            dataGridView6.DataSource = ds.Tables[0];
            conn.Close();
        }
        //修改规则
        public void Rules()
        {
            conn = new SqlConnection();
            string sql = "select * from Penaltyrules_Table";
            SqlCommand cmd = Dao.SqlConnect(sql, conn);
            try
            {
                DataSet dt = new DataSet();
                SqlDataAdapter sqlDataAdapter = new SqlDataAdapter(cmd);
                conn.Open();
                sqlDataAdapter.Fill(dt, "Penaltyrules_Table");

                textBox1.Text = dt.Tables["Penaltyrules_Table"].Rows[0]["PenaltyRate"].ToString();
                textBox2.Text = dt.Tables["Penaltyrules_Table"].Rows[1]["PenaltyRate"].ToString();

            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
            finally
            {
                conn.Close();
            }
        }
        private void tabControl1_SelectedIndexChanged(object sender, EventArgs e)
        {
            if (this.tabControl1.SelectedIndex == 3)
            {
                //切换到上下架的时候
                UpDownBook();
            }
            else if (this.tabControl1.SelectedIndex == 2)
            {
                ManageBook();
            }
            else if (this.tabControl1.SelectedIndex == 4)
            {
                VerifyRecommendation();
            }
            else if (this.tabControl1.SelectedIndex == 5)
            {
                VerifyBook();
            }
            else if (this.tabControl1.SelectedIndex == 6)
            {
                Rules();
            }
        }

        //批量导入用户信息
        private void button6_Click(object sender, EventArgs e)
        {
            System.Windows.Forms.OpenFileDialog fd = new OpenFileDialog();
            if (fd.ShowDialog() == DialogResult.OK)
            {
                string fileName = fd.FileName;
                bindReader(fileName);
            }
        }
        //将Excel的Sheet2$子表显示出来
        private void bindReader(string fileName)
        {
            string strConn = "Provider=Microsoft.ACE.OLEDB.12.0;" + "Data Source=" + fileName + ";" + "Extended Properties='Excel 8.0; HDR=Yes; IMEX=1'";
            OleDbDataAdapter da = new OleDbDataAdapter("SELECT *  FROM [Sheet2$]", strConn);
            //"Provider=Microsoft.ACE.OLEDB.12.0" +"Data Source=" + fileName + ";" +"Extended Properties=Excel 8.0";
            // "Provider=Microsoft.Jet.OLEDB.4.0;" +"Data Source=" + fileName + ";" +"Extended Properties='Excel 8.0; HDR=Yes; IMEX=1'";
            DataSet ds = new DataSet();
            try
            {
                da.Fill(ds);
                dt = ds.Tables[0];
                this.dataGridView3.DataSource = dt;
            }
            catch (Exception err)
            {
                MessageBox.Show("操作失败！" + err.ToString());
            }
        }
        //导入用户信息
        private void button7_Click(object sender, EventArgs e)
        {
            if (dataGridView3.Rows.Count > 0)
            {
                DataRow dr;
                int b = 0;
                //dt.Rows.Count
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    dr = dt.Rows[i];
                    String ReaderID = dr["ReaderID"].ToString().Trim();
                    string ReaderName = dr["ReaderName"].ToString();
                    //MessageBox.Show(ReaderID+" "+ ReaderName);
                    //上传第i条记录
                        b += insertToReader(dr);
                }
                if (b > 0)
                {
                    MessageBox.Show(b + "条入库成功");
                }
            }
            else
            {
                MessageBox.Show("没有数据！");
            }
        }
        //插入数据库的具体方法
        private int insertToReader(DataRow dr)
        {
            int i = 0;
            //excel表中的列名和数据库中的列名一定要对应  
            SqlConnection conn1 = new SqlConnection();
            string ReaderID = dr["ReaderID"].ToString().Trim();
            String sql1 = "select * from Readerinfo where ReaderID='" + ReaderID + "'";
            SqlCommand cmd1 = Dao.SqlConnect(sql1, conn1);
            conn1.Open();
            SqlDataReader sdr = cmd1.ExecuteReader();
            bool j = sdr.HasRows;
            conn1.Close();
            if (j)
            {
                MessageBox.Show("该用户已存在");
            }
            else {
                string sql = "insert into Readerinfo(ReaderID,ReaderName,ReaderTel,Major,Identy,Borrow_num,psw) values(@ReaderID,@ReaderName,@ReaderTel,@Major,@Identy,@Borrow_num,@psw)";
                SqlCommand cmd = Dao.SqlConnect(sql, conn);
                conn.Open();
                string ReaderName = dr["ReaderName"].ToString();
                string ReaderTel = dr["ReaderTel"].ToString();
                string Major = dr["Major"].ToString();
                string Identy = dr["Identy"].ToString();
                int Borrow_num = Convert.ToInt32(dr["Borrow_num"].ToString().Trim());
                string psw = dr["psw"].ToString();
                cmd.Parameters.Add("@ReaderID", SqlDbType.Char, 30).Value = ReaderID;
                cmd.Parameters.Add("@ReaderName", SqlDbType.Char, 30).Value = ReaderName;
                cmd.Parameters.Add("@ReaderTel", SqlDbType.Char, 12).Value = ReaderTel;
                cmd.Parameters.Add("@Major", SqlDbType.Char, 30).Value = Major;
                cmd.Parameters.Add("@Identy", SqlDbType.Char, 15).Value = Identy;
                cmd.Parameters.Add("@Borrow_num", SqlDbType.Int).Value = Borrow_num;
                cmd.Parameters.Add("@psw", SqlDbType.NChar, 6).Value = psw;
                i = cmd.ExecuteNonQuery();
                if (i > 0)
                {
                    dr.Delete();
                    //dataGridView4.Rows.Remove(dataGridView4.Rows[index]);//删除行 
                }
                else
                {
                    MessageBox.Show("defeat");
                }
                conn.Close();
            }
            return i;
        }

        private void dataGridView3_CellContentClick(object sender, DataGridViewCellEventArgs e)
        {

        }

        private void tabPage6_Click(object sender, EventArgs e)
        {

        }

        private void dataGridView4_CellContentClick(object sender, DataGridViewCellEventArgs e)
        {
            if (e.RowIndex >= 0)
            {
                 DataGridViewColumn column = dataGridView4.Columns[e.ColumnIndex];  //总数
                 int index = dataGridView4.CurrentCell.RowIndex;
                //这里可以编写你需要的任意关于按钮事件的操作~
                if (dataGridView4.Columns[e.ColumnIndex].Name == "修改")
                {
                    int Bid = Convert.ToInt32(dataGridView4.Rows[index].Cells[10].Value.ToString().Trim());
                    String BookName = dataGridView4.Rows[index].Cells[1].Value.ToString().Trim();
                    String Author = dataGridView4.Rows[index].Cells[2].Value.ToString();
                    String Press = dataGridView4.Rows[index].Cells[3].Value.ToString();
                    SqlConnection conn1 = new SqlConnection();
                    String sql1 = "update Binfo_Table set BookName='" + BookName + "'," + "Author='" + Author + "',Press='" + Press + "' where Bid="+ Bid ;  //需改成按Bid查找
                    SqlCommand cmd1 = Dao.SqlConnect(sql1, conn1);
                    conn1.Open();
                    int i = cmd1.ExecuteNonQuery();
                    conn1.Close();
                    if (i > 0)
                    {
                        MessageBox.Show("修改成功");
                    }
                }
                else if(dataGridView4.Columns[e.ColumnIndex].Name == "删除")
                {
                    int Bid = Convert.ToInt32(dataGridView4.Rows[index].Cells[10].Value.ToString().Trim());
                    SqlConnection conn1 = new SqlConnection();
                    String sql1 = "delete from Binfo_Table where Bid=" + Bid;  
                    SqlCommand cmd1 = Dao.SqlConnect(sql1, conn1);
                    conn1.Open();
                    int i = cmd1.ExecuteNonQuery();
                    conn1.Close();
                    if (i > 0)
                    {
                        MessageBox.Show("delete");
                        String BookNum = dataGridView4.Rows[index].Cells[7].Value.ToString().Trim();
                        sql1 = "update shelf set state=0 where number='" + BookNum+"'";  
                        cmd1 = Dao.SqlConnect(sql1, conn1);
                        conn1.Open();
                        i = cmd1.ExecuteNonQuery();
                        conn1.Close();
                        if (i > 0)
                        {
                            MessageBox.Show("删除成功");
                        }
                        dataGridView4.Rows.Remove(dataGridView4.Rows[index]);//删除行  
                    }
                  
                }
            }
        }

        private void dataGridView5_CellContentClick(object sender, DataGridViewCellEventArgs e)
        {
            if (e.RowIndex >= 0)
            {
                DataGridViewColumn column = dataGridView5.Columns[e.ColumnIndex];  //总数
                int index = dataGridView5.CurrentCell.RowIndex;
                //这里可以编写你需要的任意关于按钮事件的操作~
                if (dataGridView5.Columns[e.ColumnIndex].Name == "通过")
                {
                    int RecmdatID = Convert.ToInt32(dataGridView5.Rows[index].Cells[0].Value.ToString().Trim());
                    String RecmdatBook = dataGridView5.Rows[index].Cells[2].Value.ToString().Trim();
                    String Author = dataGridView5.Rows[index].Cells[3].Value.ToString().Trim();
                    DateTime Publication_time = Convert.ToDateTime(dataGridView5.Rows[index].Cells[4].Value.ToString().Trim());
                    String Press = dataGridView5.Rows[index].Cells[5].Value.ToString().Trim();
                    String ISBN = dataGridView5.Rows[index].Cells[7].Value.ToString().Trim();
                    String Note = dataGridView5.Rows[index].Cells[9].Value.ToString().Trim();
                    String Recmdat = dataGridView5.Rows[index].Cells[6].Value.ToString().Trim();
                    if (Recmdat!= "通过")
                    {
                    SqlConnection conn1 = new SqlConnection();
                    String sql1 = "update Purchaserecord_Table set AdmtsID='"+user.Id+"',Note='" + Note + "'," + "Recmdat='通过' where RecmdatID=" + RecmdatID;  
                    SqlCommand cmd1 = Dao.SqlConnect(sql1, conn1);
                    conn1.Open();
                    int i = cmd1.ExecuteNonQuery();
                    conn1.Close();
                    if (i > 0)
                    {
                            conn = new SqlConnection();
                            //string sql = "insert into Purchase values('" + RecmdatID + "','" + RecmdatBook + "','" + Author + "','" + Publication_time + "','" + Press + "','" + ISBN + "','未采购')";
                            string sql = "insert into Purchase(RecmdatID,RecmdatBook,Author,Publication_time,Press,ISBN,state) values(@RecmdatID,@RecmdatBook,@Author,@Publication_time,@Press,@ISBN,@state)";
                            SqlCommand cmd = Dao.SqlConnect(sql, conn);
                            conn.Open();
                            cmd.Parameters.Add("@RecmdatID", SqlDbType.Int).Value = RecmdatID;
                            cmd.Parameters.Add("@RecmdatBook", SqlDbType.Char, 30).Value = RecmdatBook;
                            cmd.Parameters.Add("@Author", SqlDbType.VarChar, 100).Value = @Author;
                            cmd.Parameters.Add("@Publication_time", SqlDbType.DateTime).Value = Publication_time;
                            cmd.Parameters.Add("@Press", SqlDbType.VarChar, 100).Value = Press;
                            cmd.Parameters.Add("@ISBN", SqlDbType.VarChar, 30).Value = ISBN;
                            cmd.Parameters.Add("@state", SqlDbType.Char,30).Value = "未采购";
                            
                            int j = cmd.ExecuteNonQuery();
                            conn.Close();
                            if (j > 0)
                            {
                                MessageBox.Show("审核通过");
                                dataGridView5.Rows[index].Cells[6].Value = "通过";
                            }
                        }
                    }
                    else
                    {
                        MessageBox.Show("审核已通过");
                    }
                    
                }
                else if (dataGridView5.Columns[e.ColumnIndex].Name == "不通过")
                {
                    int RecmdatID = Convert.ToInt32(dataGridView5.Rows[index].Cells[0].Value.ToString().Trim());
                    String Note = dataGridView5.Rows[index].Cells[9].Value.ToString().Trim();
                    String Recmdat = dataGridView5.Rows[index].Cells[6].Value.ToString().Trim();
                    if (Recmdat == "未审核")
                    {
                        SqlConnection conn1 = new SqlConnection();
                        String sql1 = "update Purchaserecord_Table set AdmtsID='" + user.Id + "',Note='" + Note + "'," + "Recmdat='不通过' where RecmdatID=" + RecmdatID;
                        SqlCommand cmd1 = Dao.SqlConnect(sql1, conn1);
                        conn1.Open();
                        int i = cmd1.ExecuteNonQuery();
                        conn1.Close();
                        if (i > 0)
                        {
                            dataGridView5.Rows[index].Cells[6].Value = "不通过";
                        }
                    }
                }
            }
        }

        private void dataGridView6_CellContentClick(object sender, DataGridViewCellEventArgs e)
        {
            if (e.RowIndex >= 0)
            {
                DataGridViewColumn column = dataGridView6.Columns[e.ColumnIndex];  //总数
                int index = dataGridView6.CurrentCell.RowIndex;
                //这里可以编写你需要的任意关于按钮事件的操作~
                if (dataGridView6.Columns[e.ColumnIndex].Name == "Column1")
                {

                    String ReplyID = dataGridView6.Rows[index].Cells[0].Value.ToString().Trim();
                    DateTime Reply_time = Convert.ToDateTime(dataGridView6.Rows[index].Cells[1].Value.ToString().Trim());
                    String ReplyState = dataGridView6.Rows[index].Cells[2].Value.ToString().Trim();
                    String ReaderID = dataGridView6.Rows[index].Cells[3].Value.ToString().Trim();
                    String BookNum = dataGridView6.Rows[index].Cells[4].Value.ToString().Trim();
                    String BorrowID = dataGridView6.Rows[index].Cells[6].Value.ToString().Trim();
                    if (ReplyState == "未审核")
                    {
                        SqlConnection conn1 = new SqlConnection();
                        String sql1 = "update Claimrecord_Table set AdmtsID='" + user.Id + "',ReplyState='通过' where ReplyID='" + ReplyID+"'";
                        SqlCommand cmd1 = Dao.SqlConnect(sql1, conn1);
                        conn1.Open();
                        int i = cmd1.ExecuteNonQuery();
                        conn1.Close();
                        if (i > 0)
                        {
                            sql1 = "update Readerinfo set Borrow_num=Borrow_num-1" + " where ReaderID='" + ReaderID + "'";
                            cmd1 = Dao.SqlConnect(sql1, conn1);
                            conn1.Open();
                            i = cmd1.ExecuteNonQuery();
                            conn1.Close();
                            if (i > 0)
                            {
                                //MessageBox.Show("用户信息修改成功");
                                sql1 = "update BorrowinfoTable set BorrowState='已归还',Return_time='"+ Reply_time + "' where BorrowID='" + BorrowID + "'";
                                cmd1 = Dao.SqlConnect(sql1, conn1);
                                conn1.Open();
                                i = cmd1.ExecuteNonQuery();
                                conn1.Close();
                                if (i > 0)
                                {
                                    //MessageBox.Show("借阅记录修改成功");
                                    sql1 = "update Binfo_Table set BookStatus='可借' where BookNum='" + BookNum + "'";
                                    cmd1 = Dao.SqlConnect(sql1, conn1);
                                    conn1.Open();
                                    i = cmd1.ExecuteNonQuery();
                                    conn1.Close();
                                    if (i > 0)
                                    {
                                        dataGridView6.Rows[index].Cells[2].Value = "通过";
                                        MessageBox.Show("成功通过");

                                    }
                                }
                            }
                        }
                    }
                }
                else if (dataGridView6.Columns[e.ColumnIndex].Name == "Column2")
                {

                    int ReplyID = Convert.ToInt32(dataGridView6.Rows[index].Cells[0].Value.ToString().Trim());
                    String ReplyState = dataGridView6.Rows[index].Cells[2].Value.ToString().Trim();
                    if (ReplyState == "未审核")
                    {
                        SqlConnection conn1 = new SqlConnection();
                        String sql1 = "update Claimrecord_Table set AdmtsID='" + user.Id + "',ReplyState='不通过', where ReplyID='" + ReplyID + "'";
                        SqlCommand cmd1 = Dao.SqlConnect(sql1, conn1);
                        conn1.Open();
                        int i = cmd1.ExecuteNonQuery();
                        conn1.Close();
                        if (i > 0)
                        {
                            MessageBox.Show("审核不通过");
                        }
                    }
                }
            }
        }

        private void comboBox1_SelectedValueChanged(object sender, EventArgs e)
        {
            String value = comboBox1.Text;
            // MessageBox.Show(value);

            SqlConnection conn = new SqlConnection();
            string sql = "select * from Borrowingrules_Table where Identy='" + value + "'";
            SqlCommand cmd = Dao.SqlConnect(sql, conn);
            try
            {
                DataSet dt = new DataSet();
                SqlDataAdapter sqlDataAdapter = new SqlDataAdapter(cmd);
                conn.Open();
                sqlDataAdapter.Fill(dt, "Borrowingrules_Table");

                textBox3.Text = dt.Tables["Borrowingrules_Table"].Rows[0]["Borrow_num"].ToString();
                textBox4.Text = dt.Tables["Borrowingrules_Table"].Rows[0]["Obook"].ToString();
                textBox5.Text = dt.Tables["Borrowingrules_Table"].Rows[0]["RenewDay"].ToString();
                textBox6.Text = dt.Tables["Borrowingrules_Table"].Rows[0]["OrderTerm"].ToString();
                textBox7.Text = dt.Tables["Borrowingrules_Table"].Rows[0]["BorrowTerm"].ToString();

            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
            finally
            {
                conn.Close();
            }
        }

        private void button8_Click(object sender, EventArgs e)
        {
            String value = comboBox1.Text;
            conn = new SqlConnection();
            string sql = "update Borrowingrules_Table set Borrow_num='" + textBox3.Text + "' ,Obook='" + textBox4.Text + "',RenewDay='" + textBox5.Text + "',OrderTerm='" + textBox6.Text + "',BorrowTerm='" + textBox7.Text + "' where Identy='" + value + "'";
            SqlCommand cmd = Dao.SqlConnect(sql, conn);
            conn.Open();
            cmd.ExecuteNonQuery();
            conn.Close();
            MessageBox.Show("借阅预定规则修改成功");
        }

        private void button9_Click(object sender, EventArgs e)
        {
            conn = new SqlConnection();
            string sql = "update Penaltyrules_Table set PenaltyRate='" + textBox1.Text + "' where FineStyle='赔书费' ";
            SqlCommand cmd = Dao.SqlConnect(sql, conn);
            conn.Open();
            cmd.ExecuteNonQuery();
            conn.Close();

            SqlConnection conn1 = new SqlConnection();
            string sql1 = "update Penaltyrules_Table set PenaltyRate='" + textBox2.Text + "' where FineStyle='逾期费' ";
            SqlCommand cmd1 = Dao.SqlConnect(sql1, conn1);
            conn1.Open();
            cmd1.ExecuteNonQuery();
            conn1.Close();
            MessageBox.Show("缴费规则修改成功");
        }

        private void textBox6_TextChanged(object sender, EventArgs e)
        {

        }

        private void label4_Click(object sender, EventArgs e)
        {

        }

        private void textBox7_TextChanged(object sender, EventArgs e)
        {

        }

        private void label5_Click(object sender, EventArgs e)
        {

        }
    }
    }

